﻿CREATE PROCEDURE [dbo].[User_Roles_AllowEdit]
	@userId UNIQUEIDENTIFIER,
	@hashTag NVARCHAR(450)
AS
BEGIN
	SET @hashTag = RTRIM(LTRIM(LOWER(@hashTag)))

	DECLARE @result BIT = 0

	IF NOT EXISTS (SELECT * FROM [dbo].[text_BookTree_Cache]	WHERE [Url] = @hashTag) OR
		NOT EXISTS (SELECT * FROM [dbo].[user_Role] AS r JOIN [dbo].[user_User] AS u ON r.[Email] = u.[Email] WHERE u.[UserId] = @userId AND r.[Role] = 'Editor')
	BEGIN
		SELECT @result AS 'AllowEdit'
		RETURN
	END

	DECLARE @t TABLE(
		[Allow] BIT
	)

	;WITH Tree AS (
		SELECT t.[BookTreeId]
					,t.[ParentBookTreeId]
		FROM [dbo].[text_BookTree_Cache] AS c
		JOIN [dbo].[text_BookTree] AS t ON c.[BookTreeId] = t.[BookTreeId]
		WHERE c.[Url] = @hashTag
	
		UNION ALL

		SELECT t.[BookTreeId]
					,t.[ParentBookTreeId]
		FROM [dbo].[text_BookTree_Cache] AS c
		JOIN [dbo].[text_BookTree] AS t ON c.[BookTreeId] = t.[BookTreeId]
		JOIN Tree AS ch ON ch.[ParentBookTreeId] = t.[BookTreeId]
		)
	INSERT @t
	SELECT CAST(ISNULL([Edit], 0) AS BIT)
	FROM Tree
	JOIN [dbo].[user_Editor] AS e ON e.[BookTreeId] = Tree.[BookTreeId]
	WHERE e.[UserId] = @userId

	IF EXISTS (SELECT * FROM @t WHERE [Allow] = 1)
		SET @result = 1

	SELECT @result AS 'AllowEdit'
END